package de.hm.edu.oradb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import de.hm.edu.cep.OrderEvent;

public class OracleDbConnectionImpl implements IDbConnection {
	
	private Connection getConnection() throws SQLException, ClassNotFoundException {

		String driver = "oracle.jdbc.driver.OracleDriver";
	    String url = "jdbc:oracle:thin:@localhost:1521:XE";
	    String username = "SYSTEM";
	    String password = "bewerber";
		Class.forName(driver);
		return DriverManager.getConnection(url, username, password);
	}
	
	/* (non-Javadoc)
	 * @see de.hm.edu.oradb.IDbConnection#insertEvent(de.hm.edu.cep.OrderEvent)
	 */
	@Override
	public int insertEvent(OrderEvent e) throws SQLException{
		
		//define query pattern
		String query = "INSERT INTO ORDEREVENTS VALUES (?,?,?,?,?)";
		
		//connect to database
		Connection conn = null;
		try {
			conn = getConnection();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PreparedStatement pstmt = conn.prepareStatement(query);
		
		//define statement values
		pstmt.setString(1, e.getId().toString());
		pstmt.setString(2, e.getTimestamp().toString());
		pstmt.setString(3, e.getProduct());
		pstmt.setInt(4, e.getAmount());
		pstmt.setString(5, e.getCausality().toString());
		
		//execute query
		int retCode = pstmt.executeUpdate();
		
		//close connections
		pstmt.close();
		conn.close();
		
		return retCode;
	}
	
	/* (non-Javadoc)
	 * @see de.hm.edu.oradb.IDbConnection#insertC2dmRecipient(java.lang.String)
	 */
	@Override
	public int insertC2dmRecipient (String registrationId) throws SQLException{
		
		//define query pattern
		String query= "INSERT INTO c2dm_recipients VALUES(c2dm_recipients_seq.nextval,?)";
		
		//connect to database
		Connection conn = null;
		try {
			conn = getConnection();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PreparedStatement pstmt = conn.prepareStatement(query);
		
		
		//define statement values
		pstmt.setString(1, registrationId);
		
		//execute query
		int retCode = pstmt.executeUpdate();
		
		//close connections and return
		pstmt.close();
		conn.close();
		
		return retCode;
	}
	
	/* (non-Javadoc)
	 * @see de.hm.edu.oradb.IDbConnection#getc2dmRecipients()
	 */
	@Override
	public ArrayList<String> getc2dmRecipients () throws SQLException{
		
		ArrayList<String> recipients = new ArrayList<String>(); 
		
		//define query pattern
		String query = "SELECT * FROM C2DM_Recipients";
		
		//connect to database
		Connection conn = null;
		try {
			conn = getConnection();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(query);
		
		while(rs.next()){
			recipients.add(rs.getString(2));
		}
		
		try {
			conn = getConnection();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		return recipients;
	}
}
